package com.amex.uam.tasks;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.amex.SQL.DBOperations;

public class ServiceHandler {
	
	private static final String ADD_USER_PROFILE_SQL = "INSERT INTO MS_USER_ADMIN ("
			+ "WORKFORCE_MBR_ID,"
			+ "USER_ADMIN_STA_CD,"
			+ "USER_GRP_ID,"
			+ "USER_STA_CD,"
			+ "USER_TYPE_CD,"
			+ "COST_CTR_CD,"
			+ "DEPT_ID,"
			+ "AMEX_BUS_CD,"
			+ "USER_ACSS_TYPE_CD,"
			+ "USER_ID,"
			+ "USER_1ST_NM,"
			+ "USER_MI_TX,"
			+ "USER_LST_NM," 
			+ "USER_EMAIL_AD_TX,"
			+ "USER_CMPL_PHONE_NO,"
			+ "WORKFORCE_MBR_MAN_1ST_NM,"
			+ "WORKFORCE_MBR_MAN_MI_TX,"
			+ "WORKFORCE_MBR_MAN_LST_NM,"
			+ "WORKFORCE_MBR_MAN_EMAIL_AD_TX,"
			+ "VP_NM,"
			+ "ACSS_RSN_TX,"
			+ "USER_ACSS_REJCT_RSN_TX,"
			+ "WORKFORCE_MBR_CLASS_CD,"
			+ "AMEX_UNIT_CD,"
			+ "LST_LOGIN_TS,"
			+ "CREAT_TS,"
			+ "LST_UPDT_TS,"
			+ "GEO_ACSS_LVL_CD"
			+ ")"
			+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public static boolean handleAddUserProfile(UserProfile up) {

		if (up == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn != null) {
			try {
				pstat = conn.prepareStatement(ADD_USER_PROFILE_SQL);
		
				pstat.setString(1, up.getWORKFORCE_MBR_ID());
				pstat.setInt(2, up.getUSER_ADMIN_STA_CD());
				pstat.setInt(3, up.getUSER_GRP_ID());
				pstat.setInt(4, up.getUSER_STA_CD());
				pstat.setInt(5, up.getUSER_TYPE_CD());
				pstat.setString(6, up.getCOST_CTR_CD());
				pstat.setString(7, up.getDEPT_ID());
				pstat.setString(8, up.getAMEX_BUS_CD());
				pstat.setString(9, up.getUSER_ACSS_TYPE_CD());
				pstat.setString(10, up.getUSER_ID());
				pstat.setString(11, up.getUSER_1ST_NM());
				pstat.setString(12, up.getUSER_MI_TX());
				pstat.setString(13, up.getUSER_LST_NM()); 
				pstat.setString(14, up.getUSER_EMAIL_AD_TX());
				pstat.setString(15, up.getUSER_CMPL_PHONE_NO());
				pstat.setString(16, up.getWORKFORCE_MBR_MAN_1ST_NM());
				pstat.setString(17, up.getWORKFORCE_MBR_MAN_MI_TX());
				pstat.setString(18, up.getWORKFORCE_MBR_MAN_LST_NM());
				pstat.setString(19, up.getWORKFORCE_MBR_MAN_EMAIL_AD_TX());
				pstat.setString(20, up.getVP_NM());
				pstat.setString(21, up.getACSS_RSN_TX());
				pstat.setString(22, up.getUSER_ACSS_REJCT_RSN_TX());
				pstat.setString(23, up.getWORKFORCE_MBR_CLASS_CD());
				pstat.setString(24, up.getAMEX_UNIT_CD());
				pstat.setTimestamp(25, up.getLST_LOGIN_TS());
				pstat.setTimestamp(26, up.getCREAT_TS());
				pstat.setTimestamp(27, up.getLST_UPDT_TS());
				pstat.setString(28, up.getGEO_ACSS_LVL_CD());
				
				int rowCount = pstat.executeUpdate();
				
				return rowCount > 0;
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// release connection
				DBOperations.disconnect(conn, pstat);
			}
			
		}
		
		return false;
			
	}
}
